@echo off
set title=bpm.patch.0.8.6.20120523.sql
echo connect to bpm user bpm using bpm; >> %title%
echo create table bpm_tr_entry_tmp like bpm_tr_entry; >> %title%
echo commit; >> %title%
echo insert into bpm_tr_entry_tmp select * from bpm_tr_entry; >> %title%
echo commit; >> %title%
echo drop table bpm_tr_entry; >> %title%
echo commit; >> %title%
echo create table bpm_tr_entry ( >> %title%
echo 	id varchar(64) not null, >> %title%
echo 	transmission_id varchar(64) not null, >> %title%
echo 	tra_field_key varchar(32), >> %title%
echo 	value varchar(10240), >> %title%
echo 	create_time timestamp >> %title%
echo ) in auto2 index in auto4; >> %title%
echo commit; >> %title%
echo insert into bpm_tr_entry select id,transmission_id,tra_field_key,value,create_time from bpm_tr_entry_tmp; >> %title%
echo commit; >> %title%
rem echo drop table bpm_tr_entry_tmp; >> %title%
echo commit; >> %title%

echo ALTER TABLE BPM_EX_FILEBASE >> %title%
echo   ALTER COLUMN ID SET DATA TYPE VARCHAR(64); >> %title%
echo ALTER TABLE BPM_EX_EXCHANGE >> %title%
echo   ALTER COLUMN ID SET DATA TYPE VARCHAR(64); >> %title%
echo ALTER TABLE BPM_EX_EXCHANGE >> %title%
echo   ALTER COLUMN FILE_ID SET DATA TYPE VARCHAR(64); >> %title%
echo ALTER TABLE BPM_EX_ATTACHMENT >> %title%
echo   ALTER COLUMN ID SET DATA TYPE VARCHAR(64); >> %title%
echo ALTER TABLE BPM_EX_ATTACHMENT >> %title%
echo   ALTER COLUMN EX_ID SET DATA TYPE VARCHAR(64); >> %title%
echo ALTER TABLE BPM_EX_RECEIVER >> %title%
echo   ALTER COLUMN ID SET DATA TYPE VARCHAR(64); >> %title%
echo ALTER TABLE BPM_EX_RECEIVER >> %title%
echo   ALTER COLUMN EXCHANGE_ID SET DATA TYPE VARCHAR(64); >> %title%
echo ALTER TABLE BPM_EX_RECEIVER >> %title%
echo   ALTER COLUMN FILE_ID SET DATA TYPE VARCHAR(64); >> %title%
echo commit; >> %title%


echo ALTER TABLE BPM_RD_READ >> %title%
echo   ALTER COLUMN ID SET DATA TYPE VARCHAR(64); >> %title%
echo ALTER TABLE BPM_RD_READER >> %title%
echo   ALTER COLUMN ID SET DATA TYPE VARCHAR(64); >> %title%
echo ALTER TABLE BPM_RD_READER >> %title%
echo   ALTER COLUMN READ_ID SET DATA TYPE VARCHAR(64); >> %title%
echo commit; >> %title%


echo ALTER TABLE BPM_FM_FOLDER >> %title%
echo   ALTER COLUMN ID SET DATA TYPE VARCHAR(64); >> %title%
echo ALTER TABLE BPM_FM_FOLDER >> %title%
echo   ALTER COLUMN PARENT_ID SET DATA TYPE VARCHAR(64); >> %title%
echo ALTER TABLE BPM_FM_FILE >> %title%
echo   ALTER COLUMN ID SET DATA TYPE VARCHAR(64); >> %title%
echo ALTER TABLE BPM_FM_FILE >> %title%
echo   ALTER COLUMN FOLDER_ID SET DATA TYPE VARCHAR(64);   >> %title%
echo commit;   >> %title%
  
echo ALTER TABLE BPM_CM_GROUP >> %title%
echo   ALTER COLUMN ID SET DATA TYPE VARCHAR(64); >> %title%
echo ALTER TABLE BPM_CM_GROUP_MEMBER >> %title%
echo   ALTER COLUMN ID SET DATA TYPE VARCHAR(64);   >> %title%
echo ALTER TABLE BPM_CM_GROUP_MEMBER >> %title%
echo   ALTER COLUMN GROUP_ID SET DATA TYPE VARCHAR(64); >> %title%
echo commit; >> %title%

echo ALTER TABLE BPM_AD_OPINION >> %title%
echo   ALTER COLUMN ID SET DATA TYPE VARCHAR(64); >> %title%
echo commit;  >> %title%

echo ALTER TABLE BPM_CM_AUTHORIZATION >> %title%
echo   ALTER COLUMN ID SET DATA TYPE VARCHAR(64); >> %title%
echo commit;  >> %title%

echo ALTER TABLE BPM_AD_WORDNO >> %title%
echo   ALTER COLUMN ID SET DATA TYPE VARCHAR(64); >> %title%
echo ALTER TABLE BPM_AD_WORDNO_DOCDEF >> %title%
echo   ALTER COLUMN ID SET DATA TYPE VARCHAR(64); >> %title%
echo ALTER TABLE BPM_AD_WORDNO_DOCDEF >> %title%
echo   ALTER COLUMN WORDNO_ID SET DATA TYPE VARCHAR(64); >> %title%
echo ALTER TABLE BPM_AD_WORDNO_DOCUMENT >> %title%
echo   ALTER COLUMN ID SET DATA TYPE VARCHAR(64); >> %title%
echo ALTER TABLE BPM_AD_WORDNO_DOCUMENT >> %title%
echo   ALTER COLUMN WORDNO_ID SET DATA TYPE VARCHAR(64); >> %title%
echo ALTER TABLE BPM_AD_WORDNO_ORG >> %title%
echo   ALTER COLUMN ID SET DATA TYPE VARCHAR(64); >> %title%
echo ALTER TABLE BPM_AD_WORDNO_ORG >> %title%
echo   ALTER COLUMN WORDNO_ID SET DATA TYPE VARCHAR(64); >> %title%
echo ALTER TABLE BPM_AD_WORDNO_TEMPLATE >> %title%
echo   ALTER COLUMN ID SET DATA TYPE VARCHAR(64); >> %title%
echo ALTER TABLE BPM_AD_WORDNO_TEMPLATE >> %title%
echo   ALTER COLUMN WORDNO_ID SET DATA TYPE VARCHAR(64); >> %title%
echo commit; >> %title%
echo CREATE INDEX BPM_EX_RECEIVER_SHORTNAME  ON BPM_EX_RECEIVER (RECEIVER_ID_SHORT); >> %title%
echo CREATE INDEX BPM_RD_READER_SHORTNAME  ON BPM_RD_READER (READER_ID_SHORT); >> %title%
echo runstats on table bpm.BPM_EX_RECEIVER with distribution and detailed indexes all ; >> %title%
echo runstats on table bpm.BPM_RD_READER with distribution and detailed indexes all ; >> %title%
echo commit; >> %title%


echo CREATE TABLE BPM_FA_FOREIGNACTIVE >> %title%
echo  (ID               VARCHAR(50)     NOT NULL, >> %title%
echo   TITLE            VARCHAR(800), >> %title%
echo   ACTIVITY_STATUS  VARCHAR(30), >> %title%
echo   MEETING_DATE     TIMESTAMP, >> %title%
echo   CHANGE_EXPLAIN   VARCHAR(100), >> %title%
echo   MEETING_HOUR     VARCHAR(30), >> %title%
echo   MEETING_MINU     VARCHAR(30), >> %title%
echo   MEETING_LANGUAGE VARCHAR(100), >> %title%
echo   MEETING_PLACE    VARCHAR(100), >> %title%
echo   TRANSLATOR       VARCHAR(200), >> %title%
echo   PERSON_NUM       VARCHAR(100), >> %title%
echo   CHINA_ATTEND     VARCHAR(200), >> %title%
echo   CHINA_JOINER     VARCHAR(200), >> %title%
echo   DRAFTER          VARCHAR(200), >> %title%
echo   MAIN_DEPT        VARCHAR(200), >> %title%
echo   FOREIGN_JOINER   VARCHAR(200), >> %title%
echo   DRAFTER_TEL      VARCHAR(200), >> %title%
echo   FOREIGN_ORG      VARCHAR(200), >> %title%
echo   FOREIGN_DUTY     VARCHAR(200), >> %title%
echo   FOREIGN_ATTEND   VARCHAR(200), >> %title%
echo   FOREIGN_COUNTRY  VARCHAR(200), >> %title%
echo   CONTACT_UNIT     VARCHAR(200), >> %title%
echo   FOREIGN_CAR      VARCHAR(200), >> %title%
echo   CONTACT_MAN      VARCHAR(200), >> %title%
echo   MEET_HISTORY     VARCHAR(4000), >> %title%
echo   ACTIVITY_BRIEF   VARCHAR(4000), >> %title%
echo   BACKGROUND       VARCHAR(4000), >> %title%
echo   INTER_OPINION    VARCHAR(2000), >> %title%
echo   ATTACH_NAME      VARCHAR(800), >> %title%
echo   ARCH_STATUS      VARCHAR(30), >> %title%
echo   GQ_TEMPLATEID    VARCHAR(50), >> %title%
echo   EDIT_FLAG        VARCHAR(30), >> %title%
echo   DEL_FLAG         VARCHAR(30), >> %title%
echo   OPER_USER        VARCHAR(100), >> %title%
echo   OPER_DATE        TIMESTAMP, >> %title%
echo   DRAFT_DATE       TIMESTAMP, >> %title%
echo   MEETING_WEEK     VARCHAR(30), >> %title%
echo   PHONE            VARCHAR(300), >> %title%
echo   CHU_LEADER       VARCHAR(200), >> %title%
echo   DEPT_OPINION     VARCHAR(2000), >> %title%
echo   REG_NO           VARCHAR(200), >> %title%
echo   PRIORITY         VARCHAR(100), >> %title%
echo   REG_DATE         TIMESTAMP, >> %title%
echo   VISIT_REASON     VARCHAR(2000), >> %title%
echo   PRINT_DATE       TIMESTAMP, >> %title%
echo   MEETING_HISTORY  VARCHAR(4000), >> %title%
echo   FOREIGN_UNIT     VARCHAR(200), >> %title%
echo   FOREIGN_NAME     VARCHAR(200), >> %title%
echo   FORM_TYPE        VARCHAR(64), >> %title%
echo   PROCESS_ID       VARCHAR(64), >> %title%
echo   DEPTHQ_OPINION   VARCHAR(64), >> %title%
echo   CON_STATUS       VARCHAR(100), >> %title%
echo   DIGAO_08         VARCHAR(10) >> %title%
echo  ) in auto2 index in auto4; >> %title%

echo CREATE TABLE BPM_FA_FOREIGNACTIVEATTACH >> %title%
echo  (ID                 VARCHAR(50)     NOT NULL, >> %title%
echo   BASEID             VARCHAR(50), >> %title%
echo   ATFILEID           VARCHAR(100), >> %title%
echo   FILETITLE          VARCHAR(400), >> %title%
echo   FILENAME           VARCHAR(400), >> %title%
echo   FILEEXNAME         VARCHAR(32), >> %title%
echo   FILESIZE           VARCHAR(32), >> %title%
echo   DEPOSITDATE        TIMESTAMP, >> %title%
echo   STATUS             VARCHAR(1), >> %title%
echo   DEPOSITUSERFULLID  VARCHAR(50), >> %title%
echo   CONTENTTYPE        VARCHAR(100), >> %title%
echo   REMARK             VARCHAR(100), >> %title%
echo   DEPOSITUSERNAME    VARCHAR(50), >> %title%
echo   UPDATEUSERFULLID   VARCHAR(50), >> %title%
echo   UPDATEUSERNAME     VARCHAR(50), >> %title%
echo   UPDATEDATE         TIMESTAMP >> %title%
echo  ) in auto2 index in auto4; >> %title%

echo  CREATE TABLE BPM_FA_RECORD >> %title%
echo  (ID              VARCHAR(50)     NOT NULL, >> %title%
echo   MAINID          VARCHAR(40), >> %title%
echo   USERID          VARCHAR(50), >> %title%
echo   USERNAME        VARCHAR(100), >> %title%
echo   USERDEPTID      VARCHAR(100), >> %title%
echo   USERDEPTNAME    VARCHAR(200), >> %title%
echo   LOGINIP         VARCHAR(128), >> %title%
echo   OPERCONTENT     VARCHAR(200), >> %title%
echo   OPERDATE        VARCHAR(100), >> %title%
echo   DELFLAG         VARCHAR(1)                DEFAULT '0', >> %title%
echo   OPERRESULT      VARCHAR(1), >> %title%
echo   TITLE           VARCHAR(400), >> %title%
echo   ACTIVITYSTATUS  VARCHAR(30), >> %title%
echo   CHANGEEXPLAIN   VARCHAR(100), >> %title%
echo   MEETINGDATE     TIMESTAMP, >> %title%
echo   MEETINGWEEK     VARCHAR(30), >> %title%
echo   MEETINGHOUR     VARCHAR(30), >> %title%
echo   MEETINGMINU     VARCHAR(30), >> %title%
echo   MEETINGPLACE    VARCHAR(100), >> %title%
echo   MEETINGLANGUAGE VARCHAR(100), >> %title%
echo   PERSONNUM       VARCHAR(100), >> %title%
echo   TRANSLATOR      VARCHAR(200), >> %title%
echo   CHINAJOINER     VARCHAR(200), >> %title%
echo   CHINAATTEND     VARCHAR(200), >> %title%
echo   MAINDEPT        VARCHAR(400), >> %title%
echo   DRAFTER         VARCHAR(400), >> %title%
echo   DRAFTERTEL      VARCHAR(400), >> %title%
echo   DRAFTDATE       TIMESTAMP, >> %title%
echo   FOREIGNJOINER   VARCHAR(400), >> %title%
echo   FOREIGNDUTY     VARCHAR(200), >> %title%
echo   FOREIGNORG      VARCHAR(400), >> %title%
echo   FOREIGNCOUNTRY  VARCHAR(400), >> %title%
echo   FOREIGNATTEND   VARCHAR(2000), >> %title%
echo   FOREIGNCAR      VARCHAR(200), >> %title%
echo   CONTACTUNIT     VARCHAR(400), >> %title%
echo   CONTACT         VARCHAR(400), >> %title%
echo   PHONE           VARCHAR(300), >> %title%
echo   ACTIVITYBRIEF   VARCHAR(4000), >> %title%
echo   MEETHISTORY     VARCHAR(4000), >> %title%
echo   BACKGROUND      VARCHAR(4000), >> %title%
echo   INTEROPINION    VARCHAR(4000), >> %title%
echo   ATTACHNAME      VARCHAR(800), >> %title%
echo   OPERUSER        VARCHAR(100), >> %title%
echo   CHULEADER       VARCHAR(400), >> %title%
echo   DEPTOPINION     VARCHAR(2000), >> %title%
echo   REGNO           VARCHAR(400), >> %title%
echo   PRIORITY        VARCHAR(100), >> %title%
echo   REGDATE         TIMESTAMP, >> %title%
echo   VISITREASON     VARCHAR(2000), >> %title%
echo   PRINTDATE       TIMESTAMP, >> %title%
echo   EDITFLAG        VARCHAR(30), >> %title%
echo   FORMTYPE        VARCHAR(64), >> %title%
echo   PROCESSID       VARCHAR(64), >> %title%
echo   CONSTATUS       VARCHAR(100) >> %title%
echo  ) in auto2 index in auto4; >> %title%

echo alter table BPM_FA_RECORD add constraint bpm_fa_record_id_pk primary key(id); >> %title%
echo alter table BPM_FA_FOREIGNACTIVEATTACH add constraint bpm_fa_foreignactiveattach_id_pk primary key(id); >> %title%
echo alter table BPM_FA_FOREIGNACTIVE add constraint bpm_fa_foreignactive_id_pk primary key(id); >> %title%
echo commit; >> %title%

echo reorg table bpm.BPM_EX_FILEBASE; >> %title%
echo reorg table bpm.BPM_EX_EXCHANGE; >> %title%
echo reorg table bpm.BPM_EX_ATTACHMENT; >> %title%
echo reorg table bpm.BPM_EX_RECEIVER; >> %title%
echo reorg table bpm.BPM_RD_READ; >> %title%
echo reorg table bpm.BPM_RD_READER; >> %title%
echo reorg table bpm.BPM_FM_FOLDER; >> %title%
echo reorg table bpm.BPM_FM_FILE; >> %title%
echo reorg table bpm.BPM_CM_GROUP; >> %title%
echo reorg table bpm.BPM_CM_GROUP_MEMBER; >> %title%
echo reorg table bpm.BPM_AD_OPINION; >> %title%
echo reorg table bpm.BPM_CM_AUTHORIZATION; >> %title%
echo reorg table bpm.BPM_AD_WORDNO; >> %title%
echo reorg table bpm.BPM_AD_WORDNO_DOCDEF; >> %title%
echo reorg table bpm.BPM_AD_WORDNO_DOCUMENT; >> %title%
echo reorg table bpm.BPM_AD_WORDNO_ORG; >> %title%
echo reorg table bpm.BPM_AD_WORDNO_TEMPLATE; >> %title%
echo terminate; >> %title%

db2cmd -c -w -i db2 -tvf %title%>result.txt

del %title%